if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetProtocolSummaries]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetProtocolSummaries]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
/********************************************************************
This procedure is used to get the active or inactive protocols depening 
on the page initiating it.

*------------------ Parameters(IN)------------------------*
@domainUserId - domainuserId for whom the protocolSummary has to be retrieved
@originatingPage - The page instantiating this procedure.
*----------------End of Parameters -----------------------*

Note:Both active and inactive protocol pages use the same procedure.
*********************************************************************/
Create procedure dbo.[GetProtocolSummaries]  
(  
 @domainuserid int,  
 @originatingpage int  
)   
as  
begin  
set nocount on  
  
 declare @activepage int  
 declare @inactivepage int  
  
 set @activepage = 0  
 set @inactivepage = 1  
  
 declare @activestatustypeid varchar(1)  
 declare @holdstatustypeid varchar(1)  
 declare @holdlapsedstatustypeid varchar(1)   
 declare @inactivestatustypeid varchar(1)  
 declare @terminatedstatustypeid varchar(1)  
   
 set @activestatustypeid = 1  
 set @holdstatustypeid = 4  
 set @holdlapsedstatustypeid = 9  
 set @inactivestatustypeid = 2  
 set @terminatedstatustypeid = 7  
  
 declare @protocolresourcetypeid varchar(1)  
 select @protocolresourcetypeid = [Id] from FwkResourceType where [Name] = 'Protocol'  
  
 declare @keyGiverAccessLevel int  
 set @keyGiverAccessLevel = 2  
  
 -- obtain the desired protocol statuses for the calling context  
 declare @statustable table  
 (   
  status int not null  
 )  
  
 if @originatingpage = @activepage   
  begin  
   insert into @statustable values (@activestatustypeid)  
   if (@@error <> 0)  
    begin  
     raiserror('insert into @statustable failed', 16, 1)  
     return  
    end  
   insert into @statustable values (@holdstatustypeid)  
   if (@@error <> 0)  
    begin  
     raiserror('insert into @statustable failed', 16, 1)  
     return  
    end  
   insert into @statustable values (@holdlapsedstatustypeid)  
   if (@@error <> 0)  
    begin  
     raiserror('insert into @statustable failed', 16, 1)  
     return  
    end  
  end  
  
 else if @originatingpage = @inactivepage   
  begin  
   insert into @statustable values (@inactivestatustypeid)  
   if (@@error <> 0)  
    begin  
     raiserror('insert into @statustable failed', 16, 1)  
     return  
    end  
   insert into @statustable values (@terminatedstatustypeid)  
   if (@@error <> 0)  
    begin  
     raiserror('insert into @statustable failed', 16, 1)  
     return  
    end  
  end  
  
 else  
  begin  
   raiserror('wrong originating context', 16, 1)  
   return  
  end  
  
  
 select protocol.[id]  
  ,rtrim(ltrim(protocol.title)) as Title
  ,protocol.expirationdate  
  ,case when len(protocol.SubStatusId) > 0 then  
   status.[name] + ', ' + substatus.[Name]   
   else status.[name] end as 'overallstatus'  
  ,protocol.pendingirb  
  ,piperson.lastname + ', ' +  piperson.firstname as 'piname'  
  ,protocol.protocolnumber  
  ,sponsor.[Name] as 'SponsorName'   
  ,protocol.PIFwkDomainOrganizationId as 'OrganizationId'    
  ,protocol.PiPersonId  
  ,protocol.Version  
  ,ara.AccessLevel   
  ,count(document.[Id]) as ConsentCount  
  ,cast((case when count(process.[Id]) = 0 then '0' else '1' end) as bit) as IsLegacy  
  ,protocol.IsLocked  
 from protocol  
  inner join dbo.fnadmqgetauthorizedresourcelist(@domainuserid, @protocolresourcetypeid) ara   
   on ara.resourceid = protocol.fwkresourceid   
  inner join ProtocolOverallStatusType status  
   on protocol.OverallStatusId = status.[id]  
  left join ProtocolSubStatusType substatus  
   on protocol.SubStatusId = substatus.[id]  
  left join admperson piperson  
   on piperson.[id] = protocol.PIPersonId  
  left join admsponsor sponsor  
   on protocol.admsponsorid = sponsor.[id]  
  left join ProtocolDocument document  
   on protocol.[Id] = document.ProtocolId and  
     document.iscurrent = 1 and document.type='ATCHTYPE_CNSNTFRM' and document.HardCopy = 0 and document.published = 1  
  left join ProtocolProcess process  
   on protocol.[Id] = process.ProtocolId and process.Type = 'PPTYPE_IR'  
 where protocol.OverallStatusId in (select * from @statustable)  
  and protocol.isCurrent = 1  
  and ara.AccessLevel !=  @keyGiverAccessLevel  
 group by protocol.[id]  
  ,protocol.title           
  ,protocol.expirationdate  
  ,protocol.SubStatusId  
  ,status.[name]  
  ,substatus.[Name]   
  ,protocol.pendingirb  
  ,piperson.lastname  
  ,piperson.firstname  
  ,protocol.protocolnumber  
  ,sponsor.[Name]  
  ,protocol.PIFwkDomainOrganizationId  
  ,protocol.PiPersonId  
  ,protocol.Version  
  ,ara.AccessLevel   
  ,protocol.IsLocked  
 order by protocolnumber desc  
  
 select max(enddate) as 'lastfeedrundate'  
 from SvcFeedRun  
 where svcfeedtypeid in (select [Id] from SvcFeedType where [Name] = 'NewProtocol_RexToAsset')  
 and svcfeedstatustypeid in (select id from SvcFeedStatusType where [Name] = 'Completed')  
end  
  